<?php

/* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */

/**
 * This file contains the PDODB_Query class.
 *
 * PHP version 5
 *
 * This file is part of PEAR::PDODB - A database abstraction layer for PHP5 built
 * using PDO. PHP version 5.1+ is recommended.
 *
 * PEAR::PDODB is free software: you can redistribute it and/or modify it under
 * the terms of the GNU Lesser General Public License as published by the Free
 * Software Foundation, either version 3 of the License, or (at your option)
 * any later version.
 *
 * PEAR::PDODB is distributed in the hope that it will be useful, but WITHOUT ANY
 * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
 * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
 * details.
 *
 * You should have received a copy of the GNU Lesser General Public License
 * along with PEAR::PDODB. If not, see <http://www.gnu.org/licenses/>.
 *
 * @package     PDODB
 * @category    Database
 * @author      Michael J. I. Jackson <mjijackson@gmail.com>
 * @copyright   2007 Michael J. I. Jackson
 * @license     http://www.gnu.org/licenses/lgpl-3.0.txt GNU LGPL 3.0
 * @version     SVN: $Id: Query.php 14 2007-12-10 22:58:02Z mjijackson $
 * @link        http://pear.php.net/packages/PDODB
 */

/**
 * A query-builder class that can be used to build basic SQL queries. For more
 * complex queries, use {@link PDODB_Connection::query()} and
 * {@link PDODB_Connection::exec()} directly.
 *
 * @package     PDODB
 * @category    Database
 * @author      Michael J. I. Jackson <mjijackson@gmail.com>
 */
class PDODB_Query
{

    /**#@+
     * Query type constants used internally to keep track of the type of this
     * query.
     *
     * @var     int
     * @access  public
     */
    const TYPE_SELECT       = 1;
    const TYPE_INSERT       = 2;
    const TYPE_UPDATE       = 3;
    const TYPE_DELETE       = 4;
    /**#@-*/

    /**#@+
     * @access  protected
     */
    /**
     * The fields to use in the query.
     *
     * @var     array
     */
    protected $fields = array();

    /**
     * The table(s) to use in the query.
     *
     * @var     array
     */
    protected $table = null;

    /**
     * The data to INSERT/UPDATE in this query.
     *
     * @var     array
     */
    protected $data = array();

    /**
     * Determines if this query is DISTINCT.
     *
     * @var     bool
     */
    protected $distinct = false;

    /**
     * The LIMIT to use in this query.
     *
     * @var     int
     */
    protected $limit = null;

    /**
     * The OFFSET to use in this query.
     *
     * @var     int
     */
    protected $offset = null;

    /**
     * The JOIN conditions to use in this query.
     *
     * @var     array
     */
    protected $join = array();

    /**
     * The WHERE conditions to use in this query.
     *
     * @var     array
     */
    protected $where = array();

    /**
     * The GROUP BY clauses to use in this query.
     *
     * @var     array
     */
    protected $group_by = array();

    /**
     * The HAVING clauses to use in this query.
     *
     * @var     array
     */
    protected $having = array();

    /**
     * The ORDER BY clauses to use in this query.
     *
     * @var     array
     */
    protected $order_by = array();
    /**@#-*/

    /**
     * Compilations of this query for different drivers. Used to save
     * recompiling the same query multiple times for the same driver.
     *
     * @var     array
     * @access  protected
     */
    protected $sql = array();

    /**
     * Determines whether or not this query needs to be recompiled. Is
     * automatically set true when this query's parameters change.
     *
     * @var     bool
     * @access  protected
     */
    protected $recompile = false;

    /**
     * The instance index of the default database connection to use with this
     * query.
     *
     * @var     int
     * @access  protected
     */
    protected $conn_id;

    /**
     * Constructor. Allows setting up a
     * {@link setDefaultConnection() default connection} to use for this query.
     *
     * @param   PDODB_Connection    $default_conn   The default connection to use
     * @access  public
     */
    public function __construct(PDODB_Connection $default_conn = null)
    {
        if ($default_conn) {
            $this->setDefaultConnection($default_conn);
        }
    }

    /**
     * Sets the default connection for this query. The default connection will
     * be used in {@link execute()} if no other connection is supplied.
     *
     * @param   PDODB_Connection    $default_conn   The default connection to use
     * @access  public
     */
    public function setDefaultConnection(PDODB_Connection $default_conn)
    {
        $this->conn_id = $default_conn->getId();
    }

    /**
     * Gets the default connection to use with this query.
     *
     * @return  PDODB_Connection        The connection to use
     * @access  public
     */
    public function getDefaultConnection()
    {
        if (isset($this->conn_id)) {
            return PDODB::getConnection($this->conn_id);
        }

        return null;
    }

    /**
     * Resets all class variables to their initial state. Automatically called
     * after every select, insert, update, and/or delete.
     *
     * @return  void
     * @access  public
     */
    public function reset()
    {
        $this->fields       = array();
        $this->table        = null;
        $this->data         = array();
        $this->distinct     = false;
        $this->limit        = 0;
        $this->offset       = 0;
        $this->join         = array();
        $this->where        = array();
        $this->having       = array();
        $this->group_by     = array();
        $this->order_by     = array();

        $this->recompile    = false;
    }

    /**
     * Compiles this query object and executes the query using the provided
     * connection object. If this query is not manipulative, the connection's
     * {@link PDODB_Connection::query() query} method will be used and the
     * resulting PDODB_Statement object will be returned. Otherwise,
     * {@link PDODB_Connection::exec() exec} will be used and this function will
     * return the number of affected rows. Note: If no $conn is supplied, this
     * query must have a {@link setDefaultConnection() default connection} or an
     * exception will be thrown.
     *
     * @param   PDODB_Connection    $conn   The database connection to use to
     *                                      execute the query
     * @param   bool                $reset  True to reset this query's
     *                                      parameters after query execution
     * @return  int/PDODB_Statement         The number of affected rows or the
     *                                      resulting PDODB_Statement object
     * @throws  PDODB_Exception             If no connection is supplied and this
     *                                      query doesn't have a default connection
     * @access  public
     */
    public function execute(PDODB_Connection $conn = null, $reset = false)
    {
        if (!$conn) {
            $conn = $this->getDefaultConnection();
            if (!$conn) {
                throw new PDODB_Exception('No connection to use with query');
            }
        }

        $sql = $this->compile($conn);

        if ($this->limit > 0) {
            if ($conn->supports('limit_queries')) {
                $conn->setLimit($this->limit, $this->offset);
            } else {
                $conn->raiseWarning(get_class($this), __FUNCTION__,
                    'Query with limit being used with connection that doesn\'t support limit queries');
            }
        }

        $ret = $this->type == PDODB_Query::TYPE_SELECT
            ? $conn->query($sql)
            : $conn->exec($sql);

        if ($reset) {
            $this->reset();
        }

        return $ret;
    }

    /**
     * Compiles this query into SQL for a specific connection. Note: If no $conn
     * is supplied, this query must have a
     * {@link setDefaultConnection() default connection} or an exception will be
     * thrown.
     *
     * @param   PDODB_Connection    $conn   The connection to compile the query for
     * @return  string                      This query's SQL statement
     * @throws  PDODB_Exception             If no connection is supplied and this
     *                                      query doesn't have a default connection
     * @access  public
     */
    public function compile(PDODB_Connection $conn = null)
    {
        if (!$conn) {
            $conn = $this->getDefaultConnection();
            if (!$conn) {
                throw new PDODB_Exception('No connection to use with query');
            }
        }

        $driver_type = $conn->driver();

        if ($this->recompile) {
            $this->sql = array(); // clear old compilations
        }
        if (isset($this->sql[$driver_type])) {
            return $this->sql[$driver_type]; // already compiled for this driver
        }

        switch ($this->type) {
            case PDODB_Query::TYPE_SELECT:
                $sql = $this->distinct == true ? 'SELECT DISTINCT ' : 'SELECT ';

                $sql .= implode(', ', $this->fields);

                $from = array();
                foreach ($this->table as $t) {
                    $from[] = $conn->quoteIdentifier($t, true);
                }
                $sql .= "\nFROM " . implode(', ', $from);

                if (count($this->join) > 0) {
                    $sql .= "\n" . implode("\n", $this->join);
                }
                if (count($this->where) > 0) {
                    $sql .= $this->compileWhere($conn);
                }
                if (count($this->group_by) > 0) {
                    $sql .= "\nGROUP BY " . implode(', ', $this->group_by);
                }
                if (count($this->having) > 0) {
                    $sql .= "\nHAVING " . implode("\n", $this->having);
                }
                if (count($this->order_by) > 0) {
                    $sql .= "\nORDER BY " . implode(', ', $this->order_by);
                }
            break;
            case PDODB_Query::TYPE_INSERT:
                $table = $conn->quoteIdentifier($this->table, true);
                $keys = implode(', ', array_keys($this->data));

                $values = array();
                foreach ($this->data as $datum) {
                    $values[] = $conn->quote($datum);
                }
                $values = implode(', ', $values);

                $sql = "INSERT INTO $table ($keys) VALUES ($values)";
            break;
            case PDODB_Query::TYPE_UPDATE:
                $tables = array();
                foreach ($this->table as $t) {
                    $tables[] = $conn->quoteIdentifier($t, true);
                }
                $tables = implode(', ', $tables);

                $values = array();
                foreach ($this->data as $column => $datum) {
                    $values[] = $column . ' = ' . $conn->quote($datum);
                }

                $sql = "UPDATE $tables SET " . implode(', ', $values);
                if (count($this->where) > 0) {
                    $sql .= $this->compileWhere($conn);
                }
            break;
            case PDODB_Query::TYPE_DELETE:
                $tables = array();
                foreach ($this->table as $t) {
                    $tables[] = $conn->quoteIdentifier($t, true);
                }

                $sql = 'DELETE FROM ' . implode(', ', $tables);
                if (count($this->where) > 0) {
                    $sql .= $this->compileWhere($conn);
                }
            break;
        }

        $this->sql[$driver_type] = $sql;
        $this->recompile = false;

        return $sql;
    }

    /**
     * Compiles this query's set of where conditions using a specific
     * connection's quoting style.
     *
     * @param   PDODB_Connection    $conn   The connection to use
     * @return  string                      The SQL WHERE portion of the query
     * @access  private
     */
    private function compileWhere(PDODB_Connection $conn)
    {
        $sql = "\nWHERE";
        foreach ($this->where as $w) {
            if (is_array($w)) {
                $sql .= "\n$w[0] $w[1] " . $conn->quote($w[2]);
            } else {
                $sql .= "\n$w";
            }
        }

        return $sql;
    }

    /**
     * Notifies the class that one of the query parameters has changed, thus
     * the query needs to be compiled again. Called automatically when the
     * query parameters change.
     *
     * @return  void
     * @access  private
     * @see     $recompile
     */
    private function notifyChange()
    {
        $this->recompile = true;
    }

    /**
     * Gets the SQL comparison operator at the end of a string.
     *
     * @param   string  $value      The string to test
     * @return  string              The SQL operator in the string
     * @access  private
     */
    private function getComparisonOperator($value)
    {
        return preg_match('/\s*(<|>|<>|[<>!]?=|IS (NOT )?(NULL|EMPTY)|(NOT )?(LIKE|IN|EXISTS))\s*$/i',
            $value, $match) ? $match[0] : null;
    }

    /**
     * Gets the logical SQL operator at the beginning of a string.
     *
     * @param   string  $value      The string to test
     * @return  string              The logical SQL operator
     * @access  private
     */
    private function getLogicalOperator($value)
    {
        return preg_match('/^\s*(AND|OR)\s*/i', $value, $match) ? $match[0] : null;
    }

    /**
     * Converts an object to an array, ignoring nested objects and arrays.
     *
     * @param   object  $obj    The object to convert
     * @return  array           An associative array containing the object's
     *                          variables
     * @access  private
     */
    private function objectToArray($obj)
    {
        $arr = array();
        $obj_vars = get_object_vars($obj);

        foreach ($obj_vars as $key => $value) {
            if (!is_object($value) && !is_array($value)) {
                $arr[$key] = $value;
            }
        }
    }

    /**
     * Generates the SELECT portion of the query and designates this query as
     * a SELECT.
     *
     * @param   mixed       $fields     The field name(s) to select in an array
     *                                  or comma-separated string
     * @param   string      $tables     The table name(s) to select from in an
     *                                  array or a comma-separated string
     * @return  PDODB_Query             This query object
     * @throws  PDODB_InsufficientDataException     If either table name(s) or
     *                                              field name(s) are not given
     * @access  public
     */
    public function select($fields, $tables)
    {
        if (empty($fields)) {
            throw new PDODB_InsufficientDataException("No fields supplied to SELECT query");
        }
        if (!is_array($fields)) {
            $fields = preg_split('/\s*,\s*/', $fields);
        }
        $this->fields = $fields;

        if (empty($tables)) {
            throw new PDODB_InsufficientDataException("No tables supplied to SELECT query");
        }
        if (!is_array($tables)) {
            $tables = preg_split('/\s*,\s*/', $tables);
        }
        $this->table = $tables;

        $this->type = PDODB_Query::TYPE_SELECT;
        $this->notifyChange();

        return $this;
    }

    /**
     * Generates the INSERT portion of the query and designates this query as
     * an INSERT.
     *
     * @param   string  $table          The table name
     * @param   mixed   $data           An array or object of data to insert
     * @return  PDODB_Query             This query object
     * @throws  PDODB_InsufficientDataException     If either a table name or
     *                                              INSERT data is not given
     * @access  public
     */
    public function insert($table, $data)
    {
        if (empty($table)) {
            throw new PDODB_InsufficientDataException("No table supplied to INSERT query");
        }
        $this->table = $table;

        if (empty($data)) {
            throw new PDODB_InsufficientDataException("No data supplied to INSERT query");
        }
        if (is_object($data)) {
            $data = $this->objectToArray($data);
        }
        $this->data = $data;

        $this->type = PDODB_Query::TYPE_INSERT;
        $this->notifyChange();

        return $this;
    }

    /**
     * Generates the UPDATE portion of the query and designates this query as
     * an UPDATE. Warning: If {@link where()} is not also called, this will
     * update ALL records in the given table!
     *
     * @param   mixed   $tables         The table name(s) to update in an array
     *                                  or comma-separated string
     * @param   mixed   $data           An array or object of data to update
     * @return  PDODB_Query             This query object
     * @throws  PDODB_InsufficientDataException     If either table name(s) or
     *                                              UPDATE data is not given
     * @access  public
     */
    public function update($tables, $data)
    {
        if (empty($tables)) {
            throw new PDODB_InsufficientDataException("No tables supplied to UPDATE query");
        }
        if (!is_array($tables)) {
            $tables = preg_split('/\s*,\s*/', $tables);
        }
        $this->table = $tables;

        if (empty($data)) {
            throw new PDODB_InsufficientDataException("No data supplied to UPDATE query");
        }
        if (is_object($data)) {
            $data = $this->objectToArray($data);
        }
        $this->data = $data;

        $this->type = PDODB_Query::TYPE_UPDATE;
        $this->notifyChange();

        return $this;
    }

    /**
     * Generates the DELETE portion of the query and designates this query as
     * a DELETE. Warning: If {@link where()} is not also called, this will
     * delete ALL records from the given table(s)!
     *
     * @param   mixed       $tables     The table name(s) to use in an array
     *                                  or a comma-separated string
     * @return  PDODB_Query             This query object
     * @throws  PDODB_InsufficientDataException     If a table name is not given
     * @access  public
     */
    public function delete($tables)
    {
        if (empty($tables)) {
            throw new PDODB_InsufficientDataException("No tables supplied to DELETE query");
        }
        if (!is_array($tables)) {
            $tables = preg_split('/\s*,\s*/', $tables);
        }
        $this->table = $tables;

        $this->type = PDODB_Query::TYPE_DELETE;
        $this->notifyChange();

        return $this;
    }

    /**
     * Sets a flag which tells the query string compiler to add DISTINCT to
     * the query.
     *
     * @param   bool    $distinct   True to add DISTINCT, false otherwise
     * @return  PDODB_Query         This query object
     * @access  public
     */
    public function distinct($distinct = true)
    {
        $this->distinct = is_bool($distinct) ? $distinct : true;
        $this->notifyChange();

        return $this;
    }

    /**
     * Generates the JOIN portion of the query.
     *
     * @param   string  $table      The table name
     * @param   string  $cond       The join condition
     * @param   string  $type       The type of join
     * @return  PDODB_Query         This query object
     * @access  public
     */
    public function join($table, $cond, $type = '')
    {
        if ($type != '') {
            $type = strtoupper(trim($type));
            $join_types = array(
                'INNER',
                'LEFT',
                'RIGHT',
                'OUTER',
                'LEFT OUTER',
                'RIGHT OUTER'
            );
            $type = in_array($type, $join_types, true) ? "$type " : '';
        }

        $this->join[] = $type . "JOIN $table ON $cond";
        $this->notifyChange();

        return $this;
    }

    /**
     * Prepares the WHERE condition of the query with quoted values. May
     * be called in several different ways:
     *
     * <code>
     * where('id', 1)
     * </code>
     *
     * Appends 'WHERE id = 1' to the query. Note: if this is called after the
     * first WHERE condition has already been set, WHERE will not be used and
     * the condition will be appended using AND.
     *
     * <code>
     * where('id', 1, 'OR')
     * </code>
     *
     * Appends 'WHERE id = 1' to the query. Note: if this is called after the
     * first WHERE condition has already been set, WHERE will not be used and
     * the condition will be appended using OR.
     *
     * <code>
     * where('AND balance >', 1)
     * </code>
     *
     * Appends 'AND balance > 1' to the query. Note: The logical SQL operator
     * may be appended to the beginning of the first parameter. Also, the
     * comparison operator may be appended to the end of the first parameter.
     *
     * <code>
     * where(array('id' => 1, 'balance >' => 200))
     * </code>
     *
     * Appends 'WHERE id = 1 AND balance > 200' to the query. Note: An array may
     * be used as the first parameter to specify multiple conditions.
     *
     * <code>
     * where(array('balance >' => 200, 'first_name LIKE' => 'Joe%'), 'OR')
     * </code>
     *
     * Appends "WHERE balance > 200 OR first_name LIKE 'Joe%'" to the query.
     * Note: When an array is passed as the first parameter, the second
     * parameter specifies the default logical operator to use.
     *
     * @param   mixed   $key        The key to use or an array of key => values
     * @param   mixed   $value      The value to match
     * @param   string  $conj       The logical SQL operator used to combine
     *                              multiple conditions
     * @return  PDODB_Query         This query object
     * @access  public
     */
    public function where($key, $value = null, $conj = 'AND')
    {
        if (is_array($key)) {
            $conj = $value; // second param is conj
        } else {
            $key = array($key => $value);
        }

        $conj = strtoupper(trim($conj));
        if ($conj != 'AND' && $conj != 'OR' && $conj != 'XOR') {
            $conj = 'AND';
        }

        foreach ($key as $k => $v) {
            if (is_numeric($k)) {
                // already prepped/quoted
                $this->where[] = $v;
            } else {
                if ($l_op = $this->getLogicalOperator($k) && count($this->where) === 0) {
                    // strip the logical operator
                    $k = substr($k, strlen($l_op));
                } elseif (count($this->where) !== 0) {
                    $k = "$conj $k";
                }
                if ($c_op = $this->getComparisonOperator($k)) {
                    $k = substr($k, 0, -strlen($c_op));
                    $c_op = trim($c_op);
                } else {
                    $c_op = '=';
                }

                $this->where[] = array($k, $c_op, $v);
            }
        }

        $this->notifyChange();

        return $this;
    }

    /**
     * Sets the GROUP BY portion of the query.
     *
     * @param   string      $by     The value to group the results by
     * @return  PDODB_Query         This query object
     * @access  public
     */
    public function groupBy($by)
    {
        if (is_string($by)) {
            $by = explode(',', $by);
        }

        foreach ($by as $val) {
            $val = trim($val);
            if ($val != '') {
                $this->group_by[] = $val;
            }
        }

        $this->notifyChange();

        return $this;
    }

    /**
     * Prepares the HAVING condition of the query. May be called in several
     * different ways:
     *
     * <code>
     * having('count(*) = 10')
     * </code>
     *
     * Appends 'HAVING count(*) = 10' to the query.
     *
     * <code>
     * having(array(
     *     'count(*) > 100',
     *     'count(*) < 10'
     * ), 'OR')
     * </code>
     *
     * Appends 'HAVING count(*) > 100 OR count(*) < 10' to the query. Note: The
     * $conj parameter specifies how multiple clauses are joined together. Also,
     * an array may be used as the first parameter to use multiple clauses.
     *
     * @param   mixed   $having     The key to use or an array of key => values
     * @param   string  $conj       The logical SQL operator to be used to
     *                              combine multiple HAVING clauses
     * @return  PDODB_Query         This query object
     * @access  public
     */
    public function having($having, $conj = 'AND')
    {
        if (!is_array($having)) {
            $having = array($having);
        }

        $conj = strtoupper(trim($conj));
        if ($conj != 'AND' && $conj != 'OR') {
            $conj = 'AND';
        }

        foreach ($having as $has) {
            if (!empty($has)) {
                if (count($this->having) === 0) {
                    if ($l_op = $this->getLogicalOperator($has)) {
                        $has = substr($has, strlen($l_op));
                    }
                    $pre = '';
                } else {
                    $pre = $conj . ' ';
                }
                $this->having[] = $pre . $has;
            }
        }

        $this->notifyChange();

        return $this;
    }

    /**
     * Sets the ORDER BY portion of the query. The $order parameter may be
     * either 'ASC' or 'DESC'.
     *
     * @param   string  $order_by   The key to order by
     * @param   string  $order      The order to use
     * @return  PDODB_Query         This query object
     * @access  public
     */
    public function orderBy($order_by, $order = '')
    {
        $order = strtoupper(trim($order));
        if ($order != 'ASC' && $order != 'DESC') {
            $order = 'ASC';
        }

        $this->order_by[] = "$order_by $order";
        $this->notifyChange();

        return $this;
    }

    /**
     * Sets the LIMIT and OFFSET of the query.
     *
     * @param   int     $limit      The limit to use
     * @param   int     $offset     The offset to use
     * @return  PDODB_Query         This query object
     * @access  public
     */
    public function limit($limit, $offset = null)
    {
        $limit = (int)$limit;
        if ($limit < 0) {
            throw new PDODB_InvalidRangeException("Limit $limit is not valid");
        }
        $this->limit = $limit;

        if (!is_null($offset)) {
            $offset = (int)$offset;
            if ($offset < 0) {
                throw new PDODB_InvalidRangeException("Offset $offset is not valid");
            }
            $this->offset = $offset;
        }

        $this->notifyChange();

        return $this;
    }

}

?>
